with billway as ( -- 运单增量数据
    select waybill_no,
           input_time,
           pick_network_code as network_code,
           pick_network_name as network_name,
           case customer_type
               when '3' then '总部共享'
               when '4' then '代理区共享'
               else "-" end  as customer_type,
           customer_code,
           customer_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt = ${pt}
      and nvl(customer_code, '-') <> '-'
      and nvl(is_void, 0) = 0
      and instr(pick_network_name, '退转') = 0
      and customer_name not like 'BEST%'
      and is_delete <> 2
),
     -- 关联客户、网点扩展表
     slices as (
         select t1.waybill_no,
                substr(t1.input_time, 1, 10)            as date_time,
                t1.network_code,
                t1.network_name,
                t1.customer_type,
                t1.customer_code,
                replace(t1.customer_name, "拼多多", "桃花岛") as customer_name,
                t2.attribute_id,
                t2.customer_create_time,
                t3.agent_code,
                t3.agent_name,
                t3.fran_code,
                t3.fran_name,
                t3.regional_code,
                t3.regional_name,
                t3.provider_id,
                t3.provider_desc,
                t3.city_id,
                t3.city_desc,
                t3.area_id,
                t3.area_desc
         from billway t1
                  left join
              ( -- 客户表  -- 627574
                  select id               as customer_id,
                         code             as customer_code,
                         attribute_id,
                         case attribute_id
                             when 343 then '普通客户'
                             when 344 then '农特市场客户'
                             when 345 then '专业市场客户'
                             when 346 then '总部营销推广活动客户'
                             when 347 then '重点项目客户'
                             else '-' end as attribute_name,
                         create_time      as customer_create_time
                  from jms_dim.dim_lmdm_sys_customer
                  where instr(nvl(name, '散客'), '测试') = 0
              ) t2
              on t1.customer_code = t2.customer_code
                  left join
              ( -- 网点扩展表 25767
                  select code                   as network_code,
                         subordinate_agent_code as agent_code,
                         subordinate_agent_name as agent_name,
                         first_franchisee_code  as fran_code,
                         first_franchisee_name  as fran_name,
                         regional_id as regional_code,
                         regional_desc as regional_name,
                         provider_id,
                         provider_desc,
                         city_id,
                         city_desc,
                         area_id,
                         area_desc
                  from jms_dim.dim_lmdm_sys_network_expand
                  where instr(subordinate_agent_code, 'Test') = 0
                    AND instr(subordinate_agent_code, '3021888') = 0
                    AND instr(subordinate_agent_code, '888888') = 0
                    AND instr(code, '888888') = 0
                    AND name not like 'BEST%'
              ) t3
              on t1.network_code = t3.network_code
     ),
     -- 票量计算
     waybill_total as (
         select date_time
              , network_code
              , max(network_name)         as network_name
              , max(customer_type)        as customer_type
              , customer_code
              , max(customer_name)        as customer_name
              , attribute_id
              , max(customer_create_time) as customer_create_time
              , regional_code as region_code
              , max(regional_name) as region_name
              , agent_code
              , max(agent_name)           as agent_name
              , fran_code
              , max(fran_name)            as fran_name
              , provider_id
              , max(provider_desc)        as provider_desc
              , city_id
              , max(city_desc)            as city_desc
              , area_id
              , max(area_desc)            as area_desc
              , count(1)                  as waybill_cnt
         from slices
         group by date_time
                , network_code
                , customer_code
                , regional_code
                , attribute_id
                , agent_code
                , fran_code
                , provider_id
                , city_id
                , area_id
     )

select date_time
     , region_name
     , region_code
     , agent_code
     , agent_name
     , fran_name
     , fran_code
     , provider_desc
     , city_desc
     , area_desc
     , network_name
     , network_code
     , customer_name
     , customer_code
     , attribute_id
     , customer_create_time
     , case
           when waybill_cnt > 5000 then 'A'
           when waybill_cnt > 1000 and waybill_cnt <= 5000 then 'B'
           when waybill_cnt > 500 and waybill_cnt <= 1000 then 'C'
           when waybill_cnt > 300 and waybill_cnt <= 500 then 'D'
           else 'E' end                                           as customer_level
     , customer_type
     , waybill_cnt
     , cast(year(next_day(DATE_ADD(date_time, -7), 'MO')) as int) as year_no
     , cast(month(date_time) as int)                              as month_no
     , cast(weekofyear(date_time) as int)                         as week_no
     , date_format(`current_timestamp`(), 'yyyy-MM-dd HH:mm:ss')  as create_time
from waybill_total;